package demo.dao;

import demo.entity.employees;
import demo.entity.managers;
import demo.entity.record;
import demo.entity.users;
import demo.mappers.empRowMapper;
import demo.mappers.managersRowMapper;
import demo.mappers.recordRowMapper;
import demo.mappers.userRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class TopDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public users addUsers(users user) {   //添加到users
        String sql = "INSERT INTO users VALUES(?,?,?,?)";
        jdbcTemplate.update(sql,user.getEmp_id(),user.getPassword(),user.getJob_id(),user.getPhone_id());
        String sql1 = "SELECT * FROM users WHERE emp_id=?";
        users ret = jdbcTemplate.queryForObject(sql1, new userRowMapper(), user.getEmp_id());
        return ret;
    }

    public employees addEmployees(employees emp) {   //添加到employees
        String sql = "INSERT INTO employees VALUES(?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,emp.getEmp_id(),emp.getName(),emp.getDepartment_id(),emp.getJob_id(),emp.getBasic_Salary(),
                emp.getArrival_time(),emp.getDeparture_time(),emp.getRest_holiday(),emp.getMonthly_salary());
        String sql1 = "SELECT * FROM employees WHERE name=?";
        employees emplo = jdbcTemplate.queryForObject(sql1, new empRowMapper(), emp.getName());
        return emplo;
    }
    public void dropEmployeesById(int id) {
        String sql = "DELETE FROM employees WHERE emp_id=?";
        jdbcTemplate.update(sql, id);
    }
    public List<employees> queryAllEmployees(){
        String sql = "SELECT * FROM employees ORDER BY department_id,job_id DESC";
        List<employees> emp = jdbcTemplate.query(sql, new empRowMapper());
        return emp;
    }
    public employees queryEmployeesById(int id) {
        String sql = "SELECT * FROM employees WHERE emp_id=? ";
        employees emp = jdbcTemplate.queryForObject(sql, new empRowMapper(), id);
        return emp;
    }
    public employees changeRestHoliday(int emp_id, int newRestHoliday){
        String sql = "UPDATE employees SET rest_holiday=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newRestHoliday,emp_id);
        return queryEmployeesById(emp_id);
    }
    public  employees changeBasic_Salary(int emp_id, int newBasic_Salary){
        String sql = "UPDATE employees SET basic_Salary=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newBasic_Salary,emp_id);
        return queryEmployeesById(emp_id);
    }
    public  employees changeMonthly_Salary(int emp_id, int newMonthly_Salary){
        String sql = "UPDATE employees SET monthly_Salary=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newMonthly_Salary,emp_id);
        return queryEmployeesById(emp_id);
    }
    public  employees changeDepartment_id(int emp_id, String newDepartment_id){
        String sql = "UPDATE employees SET department_id=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newDepartment_id,emp_id);
        return queryEmployeesById(emp_id);
    }
    public  employees changeJob_id(int emp_id, String newJob_id){
        String sql = "UPDATE employees SET job_id=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newJob_id,emp_id);
        return queryEmployeesById(emp_id);
    }
    public employees changeArrival_time(int emp_id, String Arrival_time){
        String sql = "UPDATE employees SET arrival_time=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, Arrival_time,emp_id);
        return queryEmployeesById(emp_id);
    }
    public employees changeDeparture_time(int emp_id, String departure_time){
        String sql = "UPDATE employees SET departure_time=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, departure_time,emp_id);
        return queryEmployeesById(emp_id);
    }
    public List<record> queryAllRecord() {
        String sql = "SELECT * FROM record ORDER BY id";
        return jdbcTemplate.query(sql, new recordRowMapper());
    }
    public List<managers> queryManagers(){
        String sql = "SELECT * FROM managers  ";
        List<managers> manList = jdbcTemplate.query(sql, new managersRowMapper());
        return manList;
    }
    public managers queryManagersById(int id){
        String sql = "SELECT * FROM managers WHERE id = ? ";
        managers man = jdbcTemplate.queryForObject(sql, new managersRowMapper(),id);
        return man;
    }
    public managers dealManagers(int id, String status){
        Date date = new Date();
        SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateTime = dateTimeFormat.format(date);
        String sql = "UPDATE managers SET status=?, deal_time=? WHERE id=? ";
        jdbcTemplate.update(sql, status,dateTime,id);
        return queryManagersById(id);
    }
}
